package bsf.db.base;

import bsf.base.BsfException;
import bsf.log.ErrorLog;
import bsf.log.TimeWatchLog;
import bsf.log.base.TimeWatchLogConfig;

import bsf.util.ConvertUtil;
import bsf.util.FileUtil;
import org.apache.commons.lang3.StringUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Created by chejiangyi on 2016/3/25.
 */
public abstract class DbConnBase implements AutoCloseable {
    private Connection conn;

    public DbConnBase(){}

    /** 创建连接
     @return
     */
    protected DbConnBase(Connection conn)
    {
        this.conn = conn;
    }


    /**
     * 取得数据库连接对象
     *
     * @return
     */
    public Connection getConnection() {
        return conn;
    }

    /**
     * 关闭数据库连接
     */
    public void close(){
        try
        {
            if(conn!=null&&conn.isClosed()==false)
                conn.close();
        }
        catch(Exception e)
        {
            throw new DbException("close",e,this.getClass());
        }
    }

    public void beginTransaction()
    {
        try {
            if (conn != null) {
                conn.setAutoCommit(false);
            }
        }
        catch(Exception e)
        {
            throw new DbException("beginTransaction",e,this.getClass());
        }
    }

    public void commit()
    {
        try
        {
            if(conn!=null) {
                conn.commit();
                conn.setAutoCommit(true);
            }
        }
        catch(Exception e)
        {
            throw new DbException("commit",e,this.getClass());
        }
    }


    public void rollback()
    {
        try
        {
            if(conn!=null) {
                conn.rollback();
                conn.setAutoCommit(true);
            }
        }
        catch(Exception e)
        {
            ErrorLog.write("DbConn-rollback",e,this.getClass());
            //此处不抛异常
            //throw new DbException("rollback",e);
        }
    }

    public int executeSql(final String sql,final Object[] parameterValues){
        return executeDbSqlCatch("executeSql", new IDbCatch<Integer>() {
            @Override
            public Integer invoke() throws Exception {
                PreparedStatement statement = conn.prepareStatement(sql);
                attachParameterObjects(statement, parameterValues);
                int result = statement.executeUpdate();
                //statement.clearParameters();
                return result;
            }
        }, sql, parameterValues);

    }

    public Object executeScalar(final String sql,final Object[] parameterValues){
        return executeDbSqlCatch("executeScalar", new IDbCatch<Object>() {
            @Override
            public Object invoke() throws Exception {
                Object value = null;
                ResultSet rs = executeResultSet(sql, parameterValues);
                if (rs != null&&rs.next()) {
                    value = rs.getObject(1);
                }
                return value;
            }
        }, sql, parameterValues);
    }

    public ResultSet executeResultSet(final String sql,final Object[] parameterValues) {
        return executeDbSqlCatch("executeResultSet", new IDbCatch<ResultSet>() {
            @Override
            public ResultSet invoke() throws Exception {
                PreparedStatement statement = conn.prepareStatement(sql);
                attachParameterObjects(statement, parameterValues);
                ResultSet rs = statement.executeQuery();
                //statement.clearParameters();
                return rs;
            }
        }, sql, parameterValues);
    }

    public List<Map<String, Object>> executeList(final String sql,final Object[] parameterValues) {
        return DbHelper.toMapList(executeResultSet(sql, parameterValues));
    }

//    public <T> List<T> executeList(Class<T> cls, String sql, Object[] parameterValues) {
//        return DbHelper.toList(cls, executeResultSet(sql, parameterValues));
//
// TODO: 2017/3/30  未来支持映射到实体

    public Boolean executeProcedure(final String sql,final ProcedureParameter[] parameterValues){
        return executeDbSqlCatch("executeResultSet", new IDbCatch<Boolean>() {
            @Override
            public Boolean invoke() throws Exception {
                CallableStatement statement = conn.prepareCall(sql);
                ArrayList<Integer> outIndexs = new ArrayList();
                for(int i=0;i<parameterValues.length;i++)
                {
                    if(parameterValues[i].direction== ParameterDirection.OUTPUT) {
                        statement.registerOutParameter(i+1, Types.OTHER);
                        outIndexs.add(i+1);
                    }
                }
                boolean success = statement.execute();
                if(success)
                {
                    for(int index:outIndexs) {
                        parameterValues[index].value = statement.getObject(index);
                    }
                }
                return success;
            }
        }, sql, parameterValues);
    }

    protected void attachParameterObjects(PreparedStatement statement, Object[] values) throws Exception {
        if (values != null) {
            for (int i = 0; i < values.length; i++) {
                if(values[i] instanceof  java.util.Date)
                {
                    statement.setObject(i + 1, new java.sql.Date(((java.util.Date) values[i]).getTime()));
                }
                else
                    statement.setObject(i + 1, values[i]);
            }
        }
    }

    protected <T> T executeDbSqlCatch(String methodName, IDbCatch<T> dbCatch, String sql, Object[] parameterValues)
    {
        try
        {
            if(TimeWatchLogConfig.getConfig().IsWriteLog == true) {
//                TimeWatchLogInfo timeWatchLogInfo = new TimeWatchLogInfo();
//                String url = (HttpRequestContext.getRequest() != null ? (StringHelper.subStringWithMaxLength(HttpRequestContext.getRequest().getRequestURI(), 90)) : "");
//                timeWatchLogInfo.sqlIp = "";//以后找相应api
//                timeWatchLogInfo.msg = StringUtils.trimToEmpty(sql);
//                timeWatchLogInfo.url = url;
//                timeWatchLogInfo.logTag = sql.hashCode();
//                timeWatchLogInfo.logType = TimeWatchLogTypeEnum.SQLCMD;
//                timeWatchLogInfo.remark = getParameterInfors(parameterValues);

                String message = "【Sql】"+ StringUtils.trimToEmpty(sql)+ FileUtil.lineSeparator()+"【params】"+getParameterInfors(parameterValues);
                TimeWatchLog timeWatchLog = new TimeWatchLog();
                T t = dbCatch.invoke();
                timeWatchLog.write(message);
                return t;
            }
            else
            {
                T t = dbCatch.invoke();
                return t;
            }
        }
        catch(Exception e)
        {
//            ErrorLogInfo errorLogInfo = new ErrorLogInfo();
//           errorLogInfo.developer = "";//获取项目默认
//           errorLogInfo.logTag = "";//获取项目默认
//           errorLogInfo.msg = "[sql执行]" + StringUtils.trimToEmpty(sql);
//           errorLogInfo.logType = ErrorLogTypeEnum.COMMONERROR;
//           errorLogInfo.remark = "[sql参数]"+getParameterInfors(parameterValues);

            String message = methodName +"执行出错:"+"【Sql】"+ StringUtils.trimToEmpty(sql)+ FileUtil.lineSeparator()+"【params】"+getParameterInfors(parameterValues);
            ErrorLog.write( message,e,this.getClass());
            throw new DbException(message,e,this.getClass());
        }
    }

    public java.util.Date getServerDate()
    {
        Object date = executeScalar("select GetDate() as aDate",new Object[]{});
        if(date != null)
            return ConvertUtil.objToDateTime(date);
        throw new BsfException("获取数据库服务器时间出错");
    }

    public boolean tableIsExist(String tablename)
    {
        List<Map<String, Object>> ds = executeList( "Select name from sysobjects where Name=?",new Object[]{tablename});
        if (ds==null||ds.size() == 0)
            return false;
        else
            return true;
    }

    private String getParameterInfors(Object[] parameterValues)
    {
        StringBuilder stringBuilder = new StringBuilder();
        int index = 0;
        for (Object p : parameterValues)
        {
            if(p instanceof ProcedureParameter)
            {
                index++;
                stringBuilder.append(index + ":"+ ConvertUtil.nullToStr(((ProcedureParameter)p).value)+";");
            }
            else
            {
                index++;
                stringBuilder.append(index + ":"+ConvertUtil.nullToStr(p)+";");
            }
        }
        return stringBuilder.toString();
    }
}
